IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProjectHeader]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProjectHeader]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[GetProjectHeader]
(
	@fundNumber	Varchar(30), 
	@resourceId	int,
	@budgetYear	int	= NULL
)
as
begin
	set nocount on

	set @budgetYear = isnull(@budgetYear, 0)

	if (@fundNumber = '&nbsp;') set @fundNumber=''

	if isnull(@fundNumber,'') <> ''
		begin
			select top 1 drv.* from
			(select distinct
				agf.FundNumber as FundNumber,
				isnull(agf.PILastName,'') + ', ' + isnull(agf.PIFirstName,'') as PIName,
				isnull(aa.ShortTitle,'') as ProjectName,
				agf.SponsorName as SponsorName,
				agf.SponsorNumber as SponsorId,
				coalesce(abf.BudgetStartDate,agf.BudgetStartDate,'') as BudgetStart,
				coalesce(abf.BudgetEndDate,agf.BudgetEndDate,'') as BudgetEnd,
				isnull(agf.ProjectStartDate,'') as ProjectStart,
				isnull(agf.ProjectEndDate,'') as ProjectEnd,
				CASE isnull(fst.Name, 'R')
					WHEN 'A' THEN 'Active'
					WHEN 'I' THEN 'Inactive'
					WHEN 'R' THEN 'Pending'
				END AS FundStatus,
				agf.Institution,
				agf.ChiefCode,
                Convert(varchar,ISNULL(agf.SponsorTypeId,0) ) as  SponsorTypeId  

			from dbo.AgrAgreement_Fct agf
			inner join dbo.AgrAgreement aa on aa.Id = agf.AgreementId
			left outer join dbo.AgrBudget_Fct abf on agf.FundId = abf.FundId
			and
				isnull(abf.BudgetViewTypeId, 3) = 
					isnull(
					(
						select	max(abf2.BudgetViewTypeId)
						from	dbo.AgrBudget_Fct abf2
						where	abf2.FundId = agf.FundId and abf2.BudgetViewTypeId <> 6
					),3)
			and
				isnull(abf.BudgetStartDate,'') =
				CASE
					WHEN 
						IsNull((
							select	isnull(max(abf2.BudgetStartDate),'')
							from	dbo.AgrBudget_Fct abf2
							where	abf2.FundId = agf.FundId
							and		((abf2.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf2.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
							
						),'') = '' THEN ''
				ELSE
				CASE
					WHEN @budgetYear = 0 THEN
						IsNull((
							select	max(isnull(abf3.BudgetStartDate,''))
							from	dbo.AgrBudget_Fct abf3
							where	abf3.FundId = agf.FundId
							and		((abf3.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf3.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
						),'')
					ELSE	
						IsNull((
							select	max(isnull(abf4.BudgetStartDate,''))
							from	dbo.AgrBudget_Fct abf4
							where	abf4.FundId = agf.FundId
							and		year(abf4.BudgetStartDate) = @budgetYear
							and		((abf4.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf4.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
						),'')
					END
				END
			left outer join dbo.AgrFundStatusType fst on agf.FundStatusTypeId = fst.Id
			where
				agf.FundNumber = @fundNumber) drv

		end
	else if isnull(@resourceId,-1) <> -1
		begin
			select top 1 drv.* from
			(select distinct
				isnull(agf.FundNumber,'N/A') as FundNumber,
				isnull(agf.PILastName,'') + ', ' + isnull(agf.PIFirstName,'') as PIName,
				isnull(aa.ShortTitle,'') as ProjectName,
				agf.SponsorName as SponsorName,
				agf.SponsorNumber as SponsorId,
				coalesce(abf.BudgetStartDate,agf.BudgetStartDate,'') as BudgetStart,
				coalesce(abf.BudgetEndDate,agf.BudgetEndDate,'') as BudgetEnd,
				isnull(agf.ProjectStartDate,'') as ProjectStart,
				isnull(agf.ProjectEndDate,'') as ProjectEnd,
				CASE isnull(fst.Name, 'R')
					WHEN 'A' THEN 'Active'
					WHEN 'I' THEN 'Inactive'
					WHEN 'R' THEN 'Pending'
				END AS FundStatus,
				agf.Institution,
				agf.ChiefCode,
                Convert(varchar,ISNULL(agf.SponsorTypeId,0) ) as  SponsorTypeId  

			from dbo.AgrAgreement_Fct agf
			inner join dbo.AgrAgreement aa on aa.Id = agf.AgreementId
			left outer join AgrBudget_Fct abf on agf.FundId = abf.FundId
			and
				isnull(abf.BudgetViewTypeId, 3) = 
					isnull(
					(
						select	max(abf2.BudgetViewTypeId)
						from	dbo.AgrBudget_Fct abf2
						where	abf2.FundId = agf.FundId
					),3)
			and
				isnull(abf.BudgetStartDate,'') =
				CASE
					WHEN 
						(
							select	isnull(max(abf2.BudgetStartDate),'')
							from	dbo.AgrBudget_Fct abf2
							where	abf2.FundId = agf.FundId
							and		((abf2.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf2.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
						) = '' THEN ''
				ELSE
				CASE
					WHEN @budgetYear = 0 THEN
						(
							select	max(isnull(abf2.BudgetStartDate,''))
							from	dbo.AgrBudget_Fct abf2
							where	abf2.FundId = agf.FundId
							and		((abf2.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf2.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
						)
					ELSE
								
						IsNull((
							select	max(isnull(abf4.BudgetStartDate,''))
							from	dbo.AgrBudget_Fct abf4
							where	abf4.FundId = agf.FundId
							and		year(abf4.BudgetStartDate) = @budgetYear
							and		((abf4.BudgetViewTypeId = isnull(abf.BudgetViewTypeId, 3) and agf.StatusTypeId <> 1) or
										(abf4.BudgetViewTypeId = 1 and agf.StatusTypeId = 1))
						),'')
					END
				END
			left outer join dbo.AgrFundStatusType fst on agf.FundStatusTypeId = fst.Id

			where
				@resourceId = agf.ResourceId) drv
		end
end
GO
